1. Field of the Invention
The present invention relates generally to replication of information in data processing environments and, more particularly, to system and methods for replication of stored procedure calls which occur in database systems (e.g., Oracle) that do not support the logging of stored procedure calls.
2. Description of the Background Art
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as “records” having “fields” of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information may be retrieved from or updated in such files, and so forth, all without user knowledge of the underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level.
DBMS systems have long since moved from a centralized mainframe environment to a de-centralized or distributed environment. Today, one generally finds database systems implemented as one or more PC “client” systems, for instance, connected via a network to one or more server-based database systems (SQL database server). Commercial examples of these “client/server” systems include Powersoft® clients connected to one or more Sybase® Adaptive Servers Enterprise database servers. Both Powersoft® and Sybase® Adaptive Server® Enterprise (formerly Sybase® SQL Server®) are available from Sybase, Inc. of Dublin, Calif. The general construction and operation of database management systems, including “client/server” relational database systems, is well known in the art. See e.g., Date, C., “An Introduction to Database Systems, Volume I and II”, Addison Wesley, 1990; the disclosure of which is hereby incorporated by reference.
Each day more and more users base their business operations on mission-critical systems which store information on server-based database systems, such as Sybase® Adaptive Servers Enterprise. As a result, the operations of the business are dependent upon the availability of data stored in their databases. Because of the mission-critical nature of these systems, users of these systems need to protect themselves against loss of the data due to software or hardware problems, disasters such as floods, earthquakes, or electrical power loss, or temporary unavailability of systems resulting from the need to perform system maintenance.
One well-known approach for users to guard against loss of critical business data is to maintain a standby or replicate database. A replicate database is a duplicate or mirror copy of a given database that is maintained either locally at the same site as the primary database or remotely at a different location than the primary database. The availability of a replicate copy of a given database enables a user (e.g., corporation or other business) to reconstruct a copy of a given database in the event of the loss, destruction, or unavailability of the primary database.
Today, a lot of database processing involves the invocation of database stored procedures and functions (hereinafter, collectively referred to as “stored procedures” or “procedures”) as part of ongoing transactional database processing (e.g., executing SQL statements). In the area of database replication, stored procedures pose a special set of problems. In particular, some existing database systems in wide use do not record any log information pertaining to the invocation of stored procedures. This creates problems for replication systems. Although it is possible for a replication system to indirectly examine a stored procedure invocation (i.e., by discerning changes to the underlying database tables themselves, as reflected in the log records), replication systems really have no inherent knowledge of database activities other than what they are able to discern from log records. Therefore, for purposes of replication, the absence of log information about stored procedure invocation has to date precluded some widely-used database systems from directly replicating stored procedure invocations.
Nevertheless, performance gains can be realized if direct support for replicating stored procedure invocations is provided. For example, if a stored procedure is invoked that entails substantial modification to the underlying data tables, transmission of that event to a replicate database via (voluminous) log records wastes processing and network resources. Therefore, replicating the effects (i.e., modifications) of stored procedure invocations is at best a suboptimal approach that is expensive, both in terms of bandwidth usage and computing resources. Another reason to provide direct support for the replication of stored procedure invocation is simply customer expectation. As there are existing database systems that do provide log support for stored procedure invocation (including, e.g., present assignee's Sybase Adaptive Server Enterprise), customers have simply come to expect direct support for stored procedure replication in all enterprise-level database systems. Given this customer expectation, there is great interest in providing a replication solution for legacy database systems that do not themselves support this functionality (i.e., do not support the logging of stored procedure invocation).